<?php
/**
 * Created by PhpStorm.
 * User: Steven
 * Date: 2017/6/9
 */

namespace app\educational\controller;


use app\classes\model\ClassModel;
use app\course\model\CourseModel;
use app\educational\model\StudentScoreModel;
use app\system\model\SchoolModel;
use app\financial\model\AwardStudentModel;
use app\financial\model\AwardCardModel;
use app\user\model\UserModel;
use cmf\controller\AdminBaseController;
use think\Db;
use think\Exception;
use think\Log;

class StudentScoreController extends AdminBaseController
{
    /**
     * 学生成绩列表
     * @return mixed
     */
    public function index()
    {
        $param = $this->request->param();
        $is_teacher = 0; //登录用户是否为老师的标记(0:否，1:是)
        $limit = config('limit_per_page');
        $classes = [];
        $where = [
            'ss.shop_id' => ['=', $this->admin_shop_id],
        ];

        //权限限制
        $uid = cmf_get_current_admin_id();

        //角色处理
        $class_mdl = new ClassModel();
        $roles = $class_mdl->roleAuth($uid, $this->admin_shop_id);
        if (count($roles) > 0) {
            foreach ($roles as $v) {
                if ($v['name'] == '教师') {
                    //获取当前老师的学生成绩
                    $where['cl.teacher_id'] = ['=', $uid];
                    $is_teacher = 1;
                    break;
                }
            }
        }

        if (!empty($param['school_id']) && is_numeric($param['school_id'])) {
            //学校
            $where['cl.school_id'] = intval($param['school_id']);

            //获取老师所在的班级
            $class_where = [
                'shop_id' => ['eq', $this->admin_shop_id],
                'school_id' => ['eq', $param['school_id']],
            ];
            $class = $class_mdl->getClassByField($class_where);
            if (count($class) > 0) {
                $classes = $class;
            }

            if (!empty($param['class_id']) && is_numeric($param['class_id'])) {
                //班级
                $where['ss.class_id'] = intval($param['class_id']);
            }
        }

        if (!empty($param['course_id']) && is_numeric($param['course_id'])) {
            //课程
            $where['cl.course_id'] = intval($param['course_id']);
        }

        //分页附加参数
        $page_query = [
            'school_id' => isset($param['school_id']) ? $param['school_id'] : '',
            'class_id' => isset($param['class_id']) ? $param['class_id'] : '',
            'course_id' => isset($param['course_id']) ? $param['course_id'] : '',
        ];
        //按学生成绩降序排列
        $sort = ['ss.total_score'=>'desc'];

        //获取学生成绩
        $score_mdl = new StudentScoreModel();
        $result = $score_mdl->getScore($where, $sort, $limit, $page_query);
        $data = $result['data'];

        if(count($data) > 0){
            $show_score = [
                'max_score' => $result['max'],
                'min_score' => $result['min'],
                'average_score' => $result['average'],
            ];
        }else{
            $show_score = [
                'max_score' => 0,
                'min_score' => 0,
                'average_score' => 0,
            ];
        }

        //获取学校
        $school_mdl = new SchoolModel();
        $schools = $school_mdl->getAllSchool($this->admin_shop_id);
        //获取课程
        $course_mdl = new CourseModel();
        $courses = $course_mdl->getAllCourse($this->admin_shop_id);

        $this->assign('is_teacher', $is_teacher);
        $this->assign('param', $param);
        $this->assign('classes', $classes);
        $this->assign('schools', $schools);
        $this->assign('courses', $courses);
        $this->assign('score', $data);
        $this->assign('show_score', $show_score);
        $this->assign('page', $data->render());
        return $this->fetch();
    }

    /**
     *导出
     */
    public function exportExcel()
    {
        import('phpexcel.PHPExcel', EXTEND_PATH);
        import('phpexcel.PHPExcel.IOFactory', EXTEND_PATH);
        import('phpexcel.PHPExcel.Reader.Excel5', EXTEND_PATH);

        $param = $this->request->param();
        $is_teacher = 0; //登录用户是否为老师的标记(0:否，1:是)
        $where = [
            'ss.shop_id' => ['=', $this->admin_shop_id],
            'ss.delete_time' => ['=', 0],
        ];
        $sort = ['ss.total_score'=>'desc', 'ss.id' => 'desc'];

        //TODO:权限限制
        $uid = cmf_get_current_admin_id();
        $class_mdl = new ClassModel();

        $roles = $class_mdl->roleAuth($uid, $this->admin_shop_id);
        if (count($roles) > 0) {
            foreach ($roles as $v) {
                if ($v['name'] == '教师') {
                    //获取当前老师的学生成绩
                    $where['cl.teacher_id'] = ['=', $uid];
                    $is_teacher = 1;
                    break;
                }
            }
        }

        if (!empty($param['school']) && is_numeric($param['school'])) {
            //学校
            $where['cl.school_id'] = intval($param['school']);

            if (!empty($param['class']) && is_numeric($param['class'])) {
                //班级
                $where['ss.class_id'] = intval($param['class']);
            }
        }
        if (!empty($param['course']) && is_numeric($param['course'])) {
            //课程
            $where['cl.course_id'] = intval($param['course']);
        }
        //获取学生成绩
        $score_mdl = new StudentScoreModel();
        $data = $score_mdl->alias('ss')
            ->field('ss.*,st.name as student_name,st.code,st.grade,sc.name as school_name,cl.name as class_name,cr.course_name,ac.award_money,ac.valid_date')
            ->join('student st', 'ss.student_id=st.id')
            ->join('class cl', 'ss.class_id=cl.id')
            ->join('school sc', 'cl.school_id=sc.id')
            ->join('course cr', 'cl.course_id=cr.id')
            ->join('award_student as','as.student_id=ss.student_id and as.class_id=ss.class_id','left')
            ->join('award_card ac','ac.id=as.award_id','left')
            ->where($where)
            ->order($sort)
            ->select();

        if (count($data) > 0) {
            $result = $data->toArray();
            // Create new PHPExcel object
            $objPHPExcel = new \PHPExcel();
            // Set properties
            $objPHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");

            //设置列宽
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);

            //设置行高度
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
            $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

            //设置字体
            $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->setSize(14)->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getFont()->setBold(true);
//            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//            $objPHPExcel->getActiveSheet()->getStyle('A2:P2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

            //设置水平居中
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //合并
            $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');

            // set table header content
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '学生成绩信息')
                ->setCellValue('A2', '学号')
                ->setCellValue('B2', '学生姓名')
                ->setCellValue('C2', '学校')
                ->setCellValue('D2', '班级')
                ->setCellValue('E2', '年级')
                ->setCellValue('F2', '课程')
                ->setCellValue('G2', '第一题')
                ->setCellValue('H2', '第二题')
                ->setCellValue('I2', '第三题')
                ->setCellValue('J2', '第四题')
                ->setCellValue('K2', '第五题')
                ->setCellValue('L2', '第六题')
                ->setCellValue('M2', '附加分')
                ->setCellValue('N2', '总分')
                ->setCellValue('O2', '奖学金(元)')
                ->setCellValue('P2', '奖学金有效期');

            // Miscellaneous glyphs, UTF-8
            $total = count($result);
            for ($i = 0; $i < $total; $i++) {
                switch ($result[$i]['grade']) {
                    case 1:
                        $grade = '一年级';
                        break;
                    case 2:
                        $grade = '二年级';
                        break;
                    case 3:
                        $grade = '三年级';
                        break;
                    case 4:
                        $grade = '四年级';
                        break;
                    case 5:
                        $grade = '五年级';
                        break;
                    case 6:
                        $grade = '六年级';
                        break;
                    case 7:
                        $grade = '七年级';
                        break;
                    case 8:
                        $grade = '八年级';
                        break;
                    case 9:
                        $grade = '九年级';
                        break;
                    case 10:
                        $grade = '高一';
                        break;
                    case 11:
                        $grade = '高二';
                        break;
                    default:
                        $grade = '高三';
                        break;
                }

                if(isset($result[$i]['award_money']) && isset($result[$i]['valid_date']) && $result[$i]['valid_date']>0){
                    $award_money = $result[$i]['award_money'];
                    $expire_date = date('Y-m-d', $result[$i]['valid_date']);
                }else{
                    $award_money = '';
                    $expire_date = '';
                }

                $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 3), $result[$i]['code']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 3), $result[$i]['student_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 3), $result[$i]['school_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 3), $result[$i]['class_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 3), $grade);
                $objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($i + 3), $result[$i]['course_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($i + 3), $result[$i]['question1']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($i + 3), $result[$i]['question2']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($i + 3), $result[$i]['question3']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('J' . ($i + 3), $result[$i]['question4']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('K' . ($i + 3), $result[$i]['question5']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($i + 3), $result[$i]['question6']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('M' . ($i + 3), $result[$i]['extra_score']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('N' . ($i + 3), $result[$i]['total_score']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('O' . ($i + 3), $award_money);
                $objPHPExcel->getActiveSheet(0)->setCellValue('P' . ($i + 3), $expire_date);
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':P' . ($i + 3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//                $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':P'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 3) . ':P' . ($i + 3))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(16);

            }
            // Rename sheet
            $objPHPExcel->getActiveSheet()->setTitle('学生成绩');

            // Set active sheet index to the first sheet
            $objPHPExcel->setActiveSheetIndex(0);

            ob_end_clean();//清除缓冲区,避免乱码

            // Redirect output to a client’s web browser (Excel5)
            header('Content-Type: application/vnd.ms-excel');
            $filename = '学生成绩信息' . '(' . date('Ymd-His') . ').xls';
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');

            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output');

            $this->success('导出成功', 'student_score/index');

        } else {
            $this->error('没有符合条件的学生成绩信息，导出失败', 'student_score/index');
        }
    }

    /**
     * @return \think\response\Json
     */
    public function ajaxClass()
    {
        if ($this->request->isPost()) {
            $param = $this->request->post();

            if (empty($param['school_id'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '学校不能为空',
                    'data' => []
                ];
                return json($json_data);
            }
            if (isset($param['is_teacher']) && !is_numeric($param['is_teacher'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '参数错误',
                    'data' => []
                ];
                return json($json_data);
            }

            //获取班级
            $where = [
                'shop_id' => ['eq', $this->admin_shop_id],
                'school_id' => ['eq', $param['school_id']],
            ];

            if(isset($param['is_teacher']) && $param['is_teacher'] == 1){
                //是老师
                $where['teacher_id'] = ['eq',cmf_get_current_admin_id()];
            }
            $class_mdl = new ClassModel();
            $data = $class_mdl->getClassByField($where);

            if (count($data) < 0) {
                $json_data = [
                    'code' => 0,
                    'msg' => '没有更多数据',
                    'school_id' => $param['school_id'],
                    'data' => $data
                ];
                return json($json_data);
            }

            $json_data = [
                'code' => 1,
                'msg' => 'success',
                'school_id' => $param['school_id'],
                'data' => $data
            ];
            return json($json_data);
        }
    }

    /**
     * @return \think\response\Json
     */
    public function ajax_add_class()
    {
        if ($this->request->isPost()) {
            $param = $this->request->post();
            $classes = [];

            if (empty($param['class_id'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '班级不能为空',
                    'data' => []
                ];
                return json($json_data);
            }

            //获取班级
            $class = Db::name('class')->alias('cl')
                ->distinct(true)
                ->join('course cr', 'cl.course_id=cr.id')
                ->join('class_student cs', 'cl.id=cs.class_id')
                ->join('student st', 'cs.student_id=st.id')
                ->where('cl.shop_id', '=', $this->admin_shop_id)
                ->where('cl.delete_time', '=', 0)
                ->where('cl.id', '=', $param['class_id'])
                ->field('cs.student_id,st.name as student_name,cl.course_id,cr.course_name')
                ->select();
            if (count($class) > 0) {
                $classes = $class;
            } else {
                $json_data = [
                    'code' => 0,
                    'msg' => '没有更多数据',
                    'school_id' => $param['class_id'],
                    'data' => $classes
                ];
                return json($json_data);
            }

            $json_data = [
                'code' => 1,
                'msg' => 'success',
                'school_id' => $param['class_id'],
                'data' => $classes
            ];
            return json($json_data);
        }
    }

    /*
     * 添加学生成绩
     */
    public function add($is_teacher)
    {
        $schools = [];
        if ($is_teacher != 1) {
            /****不是老师***/
            //获取学校
            $school_mdl = new SchoolModel();
            $schools = $school_mdl->getAllSchool($this->admin_shop_id);

        } else {
            //当前用户
            $cur_uid = cmf_get_current_admin_id();
            $user_mdl = new UserModel();
            //获取当前用户所属校区
            $u_sid = $user_mdl->getSchoolByUid($cur_uid, $this->admin_shop_id);
            $new_sid = array_column($u_sid,'school_id');

            //获取学校
            $school = Db::name('school')
                ->where('shop_id',$this->admin_shop_id)
                ->where('delete_time',0)
                ->where('is_school',0)
                ->whereIn('id',$new_sid)
                ->select();
            if (!$school->isEmpty()) {
                $schools = $school;
            }
        }

        $this->assign('is_teacher', $is_teacher);
        $this->assign('schools', $schools);
        return $this->fetch();
    }

    /**
     *保存学生成绩
     */
    public function addPost()
    {
        $cur_time = time();
        $validate = validate('StudentScore');
        if ($this->request->isPost()) {
            $data = $this->request->except('is_teacher,school_id,course');
            if (!$validate->check($data)) {
                $this->error($validate->getError());
            }
            //老师标志
            $is_teacher = $this->request->post('is_teacher');

            if (empty($data['class_id'])) {
                $this->error('班级不能为空');
            }
            if (empty($data['student_id'])) {
                $this->error('学生不能为空');
            }

            $score_mdl = new StudentScoreModel();
            $total = $score_mdl->where('delete_time', '=', 0)
                ->where('shop_id', '=', $this->admin_shop_id)
                ->where(['class_id' => $data['class_id']])
                ->where(['student_id' => $data['student_id']])
                ->count();
            if (isset($total) && $total >= 1) {
                $this->error('该学生成绩已存在');
            }

            $data['shop_id'] = $this->admin_shop_id;
            $data['create_time'] = $cur_time;
            $data['update_time'] = $cur_time;

            Db::startTrans();
            try{
                $id = $score_mdl->allowField(true)->insertGetId($data);
                Db::commit();
            }catch (Exception $e){
                $id = 0;
                Db::rollback();
            }
            $table = 'student_score';//数据表名
            if ($id) {
                //记录日志
                $pk = $id;
                $studentName = db::name("student")->where('id', $data['student_id'])->value('name');
                $content = lang('ADD_STUDENT_SCORE', ['name' => $studentName]);
                do_log($content, $pk, $table, $cur_time);

                $this->success('添加成功', 'student_score/index');
            } else {
                $this->error("添加失败");
            }
        }
    }


    /*
     * 编辑学生成绩
     */
    public function edit($id, $is_teacher)
    {
        if (empty($id)) {
            $this->error('参数为空', 'student_score/index');
        }

        $score_mdl = new StudentScoreModel();
        $data = $score_mdl->where('id', $id)->find();
        if (empty($data['id'])) {
            $this->error('没有该学生成绩', 'student_score/index');
        }

        $where = [
            'ss.shop_id' => ['=', $this->admin_shop_id],
            'ss.id' => ['=', $id],
        ];
        $data = $score_mdl->alias('ss')
            ->field('ss.*,st.name as student_name,st.code,st.grade,sc.name as school_name,cl.name as class_name,cr.course_name')
            ->join('student st', 'ss.student_id=st.id')
            ->join('class cl', 'ss.class_id=cl.id')
            ->join('school sc', 'cl.school_id=sc.id')
            ->join('course cr', 'cl.course_id=cr.id')
            ->where($where)
            ->find();

        $this->assign('is_teacher', $is_teacher);
        $this->assign('data', $data);
        return $this->fetch();

    }

    /*
     * 保存编辑学生成绩
     */
    public function editPost()
    {
        $cur_time = time();
        $validate = validate('StudentScore');
        if ($this->request->isPost()) {
            $data = $this->request->except('is_teacher');
            if (!$validate->check($data)) {
                $this->error($validate->getError());
            }
            $id = $data['id'];

            //老师标志
            $is_teacher = $this->request->post('is_teacher');

            $score_mdl = new StudentScoreModel();
            $filter_id = [
                'id' => ['eq', intval($id)],
            ];
            $result = $score_mdl->getScoreByField($filter_id);
            if (isset($result->id) && empty($result->id)) {
                $this->error('没有该学生成绩', 'student_score/index');
            }

            $data['update_time'] = $cur_time;

            $res = $score_mdl->editScore($data);
            $pk = $id; //主键
            $table = 'student_score';//数据表名
            if ($res) {
                //记录日志
                $studentName = db::name("student")->where('id', $data['student_id'])->value('name');
                $content = lang('EDIT_STUDENT_SCORE', ['name' => $studentName]);
                do_log($content, $pk, $table, $cur_time);

                $this->success('编辑成功', 'student_score/index', '', 2);
            } else {
                $this->error("编辑失败");
            }
        }
    }

    /*
     * 删除
     */
    public function delete()
    {
        $cur_time = time();
        $table = 'student_score';
        $score_mdl = new StudentScoreModel();

        $param = $this->request->param();
        if (isset($param['id'])) {
            $id = $this->request->param('id', 0, 'intval');
            $data = db::name('student_score')->where('id', $id)->find();
            $studentName = db::name("student")->where('id', $data['student_id'])->value('name');
            //记录日志
            $pk = $id;
            $content = lang('DELETE_STUDENT_SCORE', ['name' => $studentName]);

        } else {
            $id = $this->request->param('ids/a');
            //记录日志
            $pk = implode(',', $id);
            $content = lang('BATCH_DELETE', ['name' => '学生成绩']);
        }

        $result = $score_mdl->deleteScore($id, $cur_time);
        if ($result) {
            do_log($content, $pk, $table, $cur_time);

            $this->success("删除成功！");
        } else {
            $this->error('删除失败！');
        }
    }

    /**
     *发放奖学金
     */
    public function scholarship()
    {
        $param = $this->request->param();
        $newData = [];
        $awardStudent = new awardStudentModel();
        $awardCard = new awardCardModel();
        $studentScore= new studentScoreModel();

        if(empty($param['score_ids'])){
            $this->error('请至少选择一个学生');
        }
        if(empty($param['award_money'])){
            $this->error('奖学金金额不能为空');
        }

        $data = $studentScore->alias('ss')
            ->field('ss.student_id,ss.class_id,cl.name classname,st.name studentname,st.u_id,as.id asId')
            ->join('class cl','cl.id=ss.class_id')
            ->join('student st','st.id=ss.student_id')
            ->join('award_student as','as.class_id=ss.class_id and as.student_id=ss.student_id','left')
            ->whereIn('ss.id',$param['score_ids'])
            ->select();

        if(!$data->isEmpty()){
            $data->toArray();
        }else{
            $this->error('没有学生成绩');
        }

        foreach ($data as $v){
            //验证有没有发过奖学金
            if (isset($v['asId']) && !empty($v['asId'])){
                $this->error('学生'.$v['studentname'].'已发过奖学金了');
            }
        }
        //设置奖学金有效期一年
        $expire_date = strtotime("next year 23:59:59");

        //新建奖学金
        $newAwardCard = [
            'award_money' => trim($param['award_money']),
            'create_card_user_id' => cmf_get_current_admin_id(),
            'card_type' => 2,
            'valid_date' => $expire_date,
            'created_time' => time()
        ];
        $award_id = $awardCard->insertGetId($newAwardCard);

        //发放奖学金
        if($award_id){
            foreach ($data as $key=>$value){
                $newData[$key] = [
                    'class_id' => $value['class_id'],
                    'u_id' => empty($value['u_id']) ? 0 : $value['u_id'],
                    'student_id' => $value['student_id'],
                    'is_used' => 1,//已绑定
                    'award_id' => $award_id
                ];
            }
            $result = $awardStudent->insertAll($newData);
            if($result){
                //记录日志
                $student_names = array_column($data->toArray(),'studentname');
                $student_names = rtrim(implode(',',$student_names), ',');
                $content = lang('SCHOLARSHIP', ['name'=>$student_names, 'money'=>$newAwardCard['award_money']]);

                do_log($content, $award_id, 'award_card,award_student', time());

                $this->success('奖学金发放成功',url('StudentScore/index'));
            }else{
                $this->error('奖学金发放失败');
            }
        }else{
            $this->error('奖学金发放失败');
        }


    }
}